package com.estar.app.business.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.web.system.pojo.base.TSDepart;
import org.jeecgframework.web.system.pojo.base.TSType;
import org.jeecgframework.web.system.pojo.base.TSTypegroup;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.estar.app.business.common.service.AppService;
import com.estar.platform.caseInfo.entity.TmblCarInfoEntity;
import com.estar.platform.caseInfo.entity.TmblSurveyInfoEntity;

@Component("xlsxExportUtil")
@Scope("prototype")
public class XlsxExportUtil {

	Workbook wb = new XSSFWorkbook();
	
	@Autowired
	AppService appService;
	/*
	 * @function 创建字体
	 * 
	 * @param height:字体高度，
	 * 
	 * @param
	 * boldWeight:不加粗设为HSSFFont.BOLDWEIGHT_NORMAL，加粗设为HSSFFont.BOLDWEIGHT_BOLD
	 * 
	 * @return HSSFFont
	 */
	public Font createFont(int height, int boldWeight) {
		// fWorkbook = wb.createFont();
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) height);
		font.setBoldweight((short) boldWeight);
		font.setFontName("Arial");

		return font;
	}

	/*
	 * @function 创建字体
	 * 
	 * @param height:字体高度， 默认为不加粗
	 * 
	 * @return HSSFFont
	 */

	public Font createFont(int height) {
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) height);
		font.setFontName("Arial");

		return font;
	}

	/*
	 * @function 创建字体
	 * 
	 * @param height:字体高度，
	 * 
	 * @param
	 * boldWeight:不加粗设为HSSFFont.BOLDWEIGHT_NORMAL，加粗设为HSSFFont.BOLDWEIGHT_BOLD
	 * 
	 * @param color:字体颜色， 调用HSSFColor里面的成员值， 例如黑色用
	 * HSSFColor.BLACK.index,白色用HSSFColor.WHITE.index
	 * 
	 * @return HSSFFont
	 */

	public Font createFont(int height, int boldWeight, int color) {
		Font font = wb.createFont();
		font.setFontHeightInPoints((short) height);
		font.setBoldweight((short) boldWeight);
		font.setColor((short) color);
		font.setFontName("Arial");

		return font;
	}

	/*
	 * @function 创建字体,:字体高度默认设为14，加粗
	 * 
	 * @return HSSFFont
	 */
	public Font createFontBold() {
		return createFont(14, Font.BOLDWEIGHT_BOLD);
	}

	/*
	 * @function 创建字体
	 * 
	 * @param height:字体高度， 默认为加粗
	 * 
	 * @return HSSFFont
	 */

	public Font createFontBold(int height) {
		return createFont(height, Font.BOLDWEIGHT_BOLD);
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体， 设行居中，列居中
	 * 
	 * @return HSSFCellStyle
	 */
	public CellStyle createStyle(Font font) {
		CellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style;
	}

	public CellStyle createStyle() {
		CellStyle style = wb.createCellStyle();
		return style;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param bottom:格子下部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,
	 * HSSFCellStyle.BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param top:格子上部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param left:格子左边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param right:格子右边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param halign:水平对齐方式，HSSFCellStyle.ALIGN_CENTER
	 * 居中，HSSFCellStyle.ALIGN_LEFT 左对齐 HSSFCellStyle.ALIGN_RIGHT 右对齐
	 * 
	 * @param halign:垂直对齐方式，HSSFCellStyle.VERTICAL_CENTER
	 * 垂直居中，HSSFCellStyle.VERTICAL_TOP 顶部对齐 HSSFCellStyle.VERTICAL_BOTTOM 底部对齐
	 * 
	 * @param fore: 前景色 调用HSSFColor里面的成员值，如
	 * HSSFColor.YELLOW.index,HSSFColor.BLUE.index
	 * 
	 * @param back: 背景色 调用HSSFColor里面的成员值,如 HSSFColor.GREEN.index
	 * 
	 * @param pattern,设置填充背景颜色样式，例如 HSSFCellStyle.SOLID_FOREGROUND
	 * 
	 * @return HSSFCellStyle
	 */

	public CellStyle createStyle(Font font, int bottom, int top, int left,
			int right, int halign, int valign, int fore, int back, int pattern) {
		CellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setBorderBottom((short) bottom);
		style.setBorderTop((short) top);
		style.setBorderLeft((short) left);
		style.setBorderRight((short) right);
		style.setAlignment((short) halign);
		style.setVerticalAlignment((short) valign);
		style.setFillBackgroundColor((short) back);
		style.setFillForegroundColor((short) fore);
		style.setFillPattern((short) pattern);
		return style;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param bottom:格子下部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,
	 * HSSFCellStyle.BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param top:格子上部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param left:格子左边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param right:格子右边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param halign:水平对齐方式，HSSFCellStyle.ALIGN_CENTER
	 * 居中，HSSFCellStyle.ALIGN_LEFT 左对齐 HSSFCellStyle.ALIGN_RIGHT 右对齐
	 * 
	 * @param halign:垂直对齐方式，HSSFCellStyle.VERTICAL_CENTER
	 * 垂直居中，HSSFCellStyle.VERTICAL_TOP 顶部对齐 HSSFCellStyle.VERTICAL_BOTTOM 底部对齐
	 * 
	 * @return HSSFCellStyle
	 */

	public CellStyle createStyle(Font font, int bottom, int top, int left,
			int right, int halign, int valign) {
		CellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setBorderBottom((short) bottom);
		style.setBorderTop((short) top);
		style.setBorderLeft((short) left);
		style.setBorderRight((short) right);
		style.setAlignment((short) halign);
		style.setVerticalAlignment((short) valign);
		return style;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param bottom:格子下部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,
	 * HSSFCellStyle.BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param top:格子上部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param left:格子左边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param right:格子右边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @return HSSFCellStyle
	 */

	public CellStyle createStyle(Font font, int bottom, int top, int left,
			int right) {
		CellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setBorderBottom((short) bottom);
		style.setBorderTop((short) top);
		style.setBorderLeft((short) left);
		style.setBorderRight((short) right);
		return style;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param bottom:格子下部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,
	 * HSSFCellStyle.BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param top:格子上部线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param left:格子左边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param right:格子右边线条大小，调用HSSFCellStyle里面的成员值,
	 * 例如HSSFCellStyle.BORDER_THIN,HSSFCellStyle
	 * .BORDER_MEDIUM,HSSFCellStyle.BORDER_THICK.
	 * 
	 * @param fore: 前景色 调用HSSFColor里面的成员值，如
	 * HSSFColor.YELLOW.index,HSSFColor.BLUE.index
	 * 
	 * @param back: 背景色 调用HSSFColor里面的成员值,如 HSSFColor.GREEN.index
	 * 
	 * @param pattern,设置填充背景颜色样式，例如 HSSFCellStyle.SOLID_FOREGROUND
	 * 
	 * @return HSSFCellStyle
	 */

	public CellStyle createStyle(Font font, int bottom, int top, int left,
			int right, int fore, int back, int pattern) {
		CellStyle style = wb.createCellStyle();
		style.setFont(font);
		style.setBorderBottom((short) bottom);
		style.setBorderTop((short) top);
		style.setBorderLeft((short) left);
		style.setBorderRight((short) right);
		style.setFillBackgroundColor((short) back);
		style.setFillForegroundColor((short) fore);
		style.setFillPattern((short) pattern);
		return style;
	}

	/**
	 * 时间格式的单元格格式化
	 * 
	 * @return
	 */
	public CellStyle createDateCellStyle(CreationHelper createHelper) {
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(
				"m/d/yy h:mm"));
		return cellStyle;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param fore: 前景色 调用HSSFColor里面的成员值，如
	 * HSSFColor.YELLOW.index,HSSFColor.BLUE.index
	 * 
	 * @param back: 背景色 调用HSSFColor里面的成员值,如 HSSFColor.GREEN.index 格子四围线条大小都设为
	 * HSSFCellStyle.BORDER_THIN， 水平居中，垂直居中
	 * 
	 * @return HSSFCellStyle
	 */
	public CellStyle createStyleThinFrame(Font font3, int fore, int back) {
		CellStyle style = createStyle(font3, HSSFCellStyle.BORDER_THIN,
				HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN,
				HSSFCellStyle.BORDER_THIN, HSSFCellStyle.ALIGN_CENTER,
				HSSFCellStyle.VERTICAL_CENTER, fore, back,
				HSSFCellStyle.SOLID_FOREGROUND);
		return style;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param fore: 前景色 调用HSSFColor里面的成员值，如
	 * HSSFColor.YELLOW.index,HSSFColor.BLUE.index
	 * 
	 * @param back: 背景色 调用HSSFColor里面的成员值,如 HSSFColor.GREEN.index 格子四围线条大小都设为
	 * HSSFCellStyle.BORDER_THIN， 水平左对齐，垂直底部对齐
	 * 
	 * @return HSSFCellStyle
	 */
	public CellStyle createStyleThinFrame2(Font font, int fore, int back) {
		CellStyle style = createStyle(font, CellStyle.BORDER_THIN,
				CellStyle.BORDER_THIN, CellStyle.BORDER_THIN,
				CellStyle.BORDER_THIN, CellStyle.ALIGN_LEFT,
				CellStyle.VERTICAL_BOTTOM, fore, back,
				CellStyle.SOLID_FOREGROUND);
		return style;
	}

	/*
	 * @function:创建式样
	 * 
	 * @param font:字体，
	 * 
	 * @param fore: 前景色 调用HSSFColor里面的成员值，如
	 * HSSFColor.YELLOW.index,HSSFColor.BLUE.index
	 * 
	 * @param back: 背景色 调用HSSFColor里面的成员值,如 HSSFColor.GREEN.index 格子四围线条大小都设为
	 * HSSFCellStyle.BORDER_THIN， 水平居中，垂直居中
	 * 
	 * @return HSSFCellStyle
	 */
	public CellStyle createStyleThinFrame3(Font font, int fore, int back) {
		CellStyle style = createStyle(font, CellStyle.BORDER_MEDIUM,
				CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM,
				CellStyle.BORDER_MEDIUM, CellStyle.ALIGN_CENTER,
				CellStyle.VERTICAL_CENTER, fore, back,
				CellStyle.SOLID_FOREGROUND);
		return style;
	}

	/*
	 * 创建行
	 * 
	 * @param sheet
	 * 
	 * @param rowIndex 行号,从0开始
	 * 
	 * @return 行
	 */
	public Row createRow(Sheet sheet1, int rowIndex) {
		return sheet1.createRow(rowIndex);
	}

	/*
	 * 设默认的列宽
	 */
	public void setDefaultColumnWidth(Sheet sheet, int width) {
		sheet.setDefaultColumnWidth((short) width);
	}

	/*
	 * @function 设列宽
	 * 
	 * @columnIndex 第几列，从0开始
	 * 
	 * @width 宽度，一般为1000~10000
	 */
	public void setColumnWidth(Sheet sheet, int columnIndex, int width) {
		sheet.setColumnWidth((short) columnIndex, (short) width);
	}

	/*
	 * 创建cell cell.setEncoding(HSSFCell.ENCODING_UTF_16) 为显示非欧美符号
	 * 
	 * @param row 将cell添加到所在的行row中
	 * 
	 * @cellIndex 行中的第几格，从0开始
	 * 
	 * @value cell显示的值
	 * 
	 * @return HSSFCell
	 */
	public Cell createCell(Row row2, int cellIndex, String value) {
		Cell cell = row2.createCell((short) cellIndex);
		// 显示中文。
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(value);
		return cell;
	}

	/*
	 * 创建cell cell.setEncoding(HSSFCell.ENCODING_UTF_16) 为显示非欧美符号
	 * 
	 * @param row 将cell添加到所在的行row中
	 * 
	 * @cellIndex 行中的第几格，从0开始 cell尚未设值
	 * 
	 * @return HSSFCell
	 */

	public Cell createCell(Row row, int cellIndex) {
		Cell cell = row.createCell((short) cellIndex);
		// 显示中文。
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		return cell;
	}

	/*
	 * @function 合并单元格并添加到表中
	 * 
	 * @param sheet 将合并后的单元格region添加到表sheet里面
	 * 
	 * @param text 单元格region的值
	 * 
	 * @param style 单元格region的式样
	 * 
	 * @param row1 单元格region的开始行
	 * 
	 * @param col1 单元格region的开始列
	 * 
	 * @param row2 单元格region的结束行
	 * 
	 * @param col2 单元格region的结束列
	 */
	public void addRegion(Sheet sheet, String text, CellStyle style, int row1,
			int col1, int row2, int col2) {
		for (int r = row1; r <= row2; r++) {
			Row row = createRow(sheet, r);
			row.setHeight((short) (50 * 20));
			for (int c = col1; c <= col2; c++) {
				Cell cell = createCell(row, c);
				cell.setCellStyle(style);
				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(text);
			}
		}
		Region region = new Region(row1, (short) col1, row2, (short) col2);
		// sheet.addMergedRegion(arg0);
		// sheet.addMergedRegion(region);
	}

	/*
	 * @function 改变单元格的文字
	 * 
	 * @param sheet 表
	 * 
	 * @param rowIndex 第几行
	 * 
	 * @param cellIndex 第几列
	 * 
	 * @param value 文字
	 */
	public void alterCell(Sheet sheet, int rowIndex, int cellIndex, String value) {
		Cell cell = sheet.getRow(rowIndex).getCell((short) cellIndex);
		// cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
		cell.setCellValue(value);
	}

	/*
	 * @function 改变单元格的文字和样式
	 * 
	 * @param sheet 表
	 * 
	 * @param rowIndex 第几行
	 * 
	 * @param cellIndex 第几列
	 * 
	 * @param value 文字
	 * 
	 * @param style 样式
	 */
	public void alterCell(Sheet sheet, int rowIndex, int cellIndex,
			String value, CellStyle style) {
		Cell cell = sheet.getRow(rowIndex).getCell((short) cellIndex);
		// cell.setEncoding(HSSFWorkbook.ENCODING_UTF_16);
		cell.setCellValue(value);
		cell.setCellStyle(style);
	}

	public void createCarExcel(String filePath, List<TmblCarInfoEntity> list){
		try {
			File f= new File(filePath) ; 			// 声明File对象
			OutputStream out;
			out = new FileOutputStream(f);
			Workbook wb = new XSSFWorkbook(); 				// 获得原始文档   
			outputCarInfoExcel(out,wb,list);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 车辆信息导出
	 * @throws IOException 
	 */
	public void outputCarInfoExcel(OutputStream os, Workbook hwk,
			List<TmblCarInfoEntity> list) throws IOException  {
	try{
		// 创建excel
		if (hwk == null) {
			wb = new XSSFWorkbook();
		} else {
			wb = hwk;
		}
		// 创建表
		Sheet sheet = wb.createSheet();
		// 设表名为中文名字,要ENCODING_UTF_16编码， 默认编码是用 ENCODING_COMPRESSED_UNICODE

		int rowIndex = 0;
		// 创建行
		// 创建字体
		Font font1 = createFontBold();
		Font font2 = createFont(8, HSSFFont.BOLDWEIGHT_NORMAL,
				HSSFColor.GREY_80_PERCENT.index);
		Font font3 = createFont(10, HSSFFont.BOLDWEIGHT_BOLD);
		Font font4 = createFont(11, HSSFFont.BOLDWEIGHT_BOLD,
				HSSFColor.BLUE.index);
		// 创建式样
		CellStyle style2 = createStyle(font2, HSSFCellStyle.BORDER_THIN,
				HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN,
				HSSFCellStyle.BORDER_THIN, HSSFCellStyle.ALIGN_CENTER,
				HSSFCellStyle.VERTICAL_CENTER, HSSFColor.BLUE.index,
				HSSFColor.RED.index, (short) 0);
		style2.setWrapText(true);

		CellStyle style4 = createStyleThinFrame(font4,
				HSSFColor.GREY_25_PERCENT.index,
				HSSFColor.GREY_25_PERCENT.index);
		style4.setWrapText(true);

		Row row2 = createRow(sheet, rowIndex);
		// 创建格

		Cell cell1 = createCell(row2, 0, "序号");
		Cell cell2 = createCell(row2, 1, "案件流水号");
		Cell cell3 = createCell(row2, 2, "车牌号");
		Cell cell4 = createCell(row2, 3, "驾驶员");
		Cell cell5 = createCell(row2, 4, "联系电话");
		Cell cell6 = createCell(row2, 5, "交强险承保公司");
		Cell cell7 = createCell(row2, 6, "商业险承保公司");
		Cell cell8 = createCell(row2, 7, "事故责任");
		Cell cell9 = createCell(row2, 8, "碰撞部位");
		Cell cell10 = createCell(row2, 9, "车辆种类");
		Cell cell11 = createCell(row2, 10, "快赔案件");
		Cell cell12 = createCell(row2, 11, "赔偿方式");
		Cell cell13 = createCell(row2, 12, "互碰自赔");
		Cell cell14 = createCell(row2, 13, "无责直赔");
		Cell cell15 = createCell(row2, 14, "被保险人");
		Cell cell16 = createCell(row2, 15, "领款人");
		Cell cell17 = createCell(row2, 16, "开户行");
		Cell cell18 = createCell(row2, 17, "卡号");
		Cell cell19 = createCell(row2, 18, "创建时间");

		cell1.setCellStyle(style4);
		cell2.setCellStyle(style4);
		cell3.setCellStyle(style4);
		cell4.setCellStyle(style4);
		cell5.setCellStyle(style4);
		cell6.setCellStyle(style4);
		cell7.setCellStyle(style4);
		cell8.setCellStyle(style4);
		cell9.setCellStyle(style4);
		cell10.setCellStyle(style4);
		cell11.setCellStyle(style4);
		cell12.setCellStyle(style4);
		cell13.setCellStyle(style4);
		cell14.setCellStyle(style4);
		cell15.setCellStyle(style4);
		cell16.setCellStyle(style4);
		cell17.setCellStyle(style4);
		cell18.setCellStyle(style4);
		cell19.setCellStyle(style4);

		int index = 1;
		for (TmblCarInfoEntity carInfo : list) {
			rowIndex++;
			int celIndex = -1;
			Row rowList = createRow(sheet, rowIndex);

			Cell cellList0 = createCell(rowList, celIndex + 1, (index++) + "");
			Cell cellList1 = createCell(rowList, celIndex + 2,
					carInfo.getReportNo());
			Cell cellList2 = createCell(rowList, celIndex + 3,
					carInfo.getCarNo());
			Cell cellList3 = createCell(rowList, celIndex + 4,
					carInfo.getDriverNme());
			Cell cellList4 = createCell(rowList, celIndex + 5,
					"***********");
			
			//公司代码
			TSDepart depart = appService.findUniqueByProperty(TSDepart.class, "orgCode",carInfo.getCompulsoryCompany());
			Cell cellList5 = createCell(rowList, celIndex + 6,
					depart.getDepartname());
			
			TSDepart depart2  = appService.findUniqueByProperty(TSDepart.class, "orgCode",carInfo.getBusinessCompany());
			Cell cellList6 = createCell(rowList, celIndex + 7,
					depart2.getDepartname());
			
			
			Cell cellList7 = createCell(rowList, celIndex + 8,
					carInfo.getAccidentLiability());
			Cell cellList8 = createCell(rowList, celIndex + 9,
					carInfo.getSiteType());
			Cell cellList9 = createCell(rowList, celIndex + 10,
					carInfo.getCarType());
			Cell cellList10 = createCell(rowList, celIndex + 11,
					carInfo.getQuickCase());
			Cell cellList11 = createCell(rowList, celIndex + 12,
					carInfo.getCmethod());
			Cell cellList12 = createCell(rowList, celIndex + 13,
					carInfo.getMtasc());
			Cell cellList13 = createCell(rowList, celIndex + 14,
					carInfo.getDirectc());
			Cell cellList14 = createCell(rowList, celIndex + 15,
					carInfo.getInsuredPerson());
			Cell cellList15 = createCell(rowList, celIndex + 16,
					carInfo.getPayeePerson());
			Cell cellList16 = createCell(rowList, celIndex + 17,
					carInfo.getBankNme());
			Cell cellList17 = createCell(rowList, celIndex + 18,
					carInfo.getBankNo());
			Cell cellList18 = createCell(rowList, celIndex + 19,
					new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(carInfo.getCreateTm()));

			cellList0.setCellStyle(style2);
			cellList1.setCellStyle(style2);
			cellList2.setCellStyle(style2);
			cellList3.setCellStyle(style2);
			cellList4.setCellStyle(style2);
			cellList5.setCellStyle(style2);
			cellList6.setCellStyle(style2);
			cellList7.setCellStyle(style2);
			cellList8.setCellStyle(style2);
			cellList9.setCellStyle(style2);
			cellList10.setCellStyle(style2);
			cellList11.setCellStyle(style2);
			cellList12.setCellStyle(style2);
			cellList13.setCellStyle(style2);
			cellList14.setCellStyle(style2);
			cellList15.setCellStyle(style2);
			cellList16.setCellStyle(style2);
			cellList17.setCellStyle(style2);
			cellList18.setCellStyle(style2);

		}

		wb.write(os);
		os.close();
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		if(os!=null){
			os.close();
		}
	}	
	}

	
	public void createCaseExcel(String filePath, List<TmblSurveyInfoEntity> list){
		try {
			File f= new File(filePath) ; 			// 声明File对象
			OutputStream out;
			out = new FileOutputStream(f);
			Workbook wb = new XSSFWorkbook(); 				// 获得原始文档   
			outputCaseInfoExcel(out,wb,list);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 案件信息导出
	 * @throws Exception 
	 */
	public void outputCaseInfoExcel(OutputStream os, Workbook hwk,
			List<TmblSurveyInfoEntity> list) throws Exception  {
		try{
			// 创建excel
			if (hwk == null) {
				wb = new XSSFWorkbook();
			} else {
				wb = hwk;
			}
			// 创建表
			Sheet sheet = wb.createSheet();
			// 设表名为中文名字,要ENCODING_UTF_16编码， 默认编码是用 ENCODING_COMPRESSED_UNICODE
	
			int rowIndex = 0;
			// 创建行
			// 创建字体
			Font font1 = createFontBold();
			Font font2 = createFont(8, HSSFFont.BOLDWEIGHT_NORMAL,
					HSSFColor.GREY_80_PERCENT.index);
			Font font3 = createFont(10, HSSFFont.BOLDWEIGHT_BOLD);
			Font font4 = createFont(11, HSSFFont.BOLDWEIGHT_BOLD,
					HSSFColor.BLUE.index);
			// 创建式样
			CellStyle style2 = createStyle(font2, HSSFCellStyle.BORDER_THIN,
					HSSFCellStyle.BORDER_THIN, HSSFCellStyle.BORDER_THIN,
					HSSFCellStyle.BORDER_THIN, HSSFCellStyle.ALIGN_CENTER,
					HSSFCellStyle.VERTICAL_CENTER, HSSFColor.BLUE.index,
					HSSFColor.RED.index, (short) 0);
			style2.setWrapText(true);
	
			CellStyle style4 = createStyleThinFrame(font4,
					HSSFColor.GREY_25_PERCENT.index,
					HSSFColor.GREY_25_PERCENT.index);
			style4.setWrapText(true);
	
			Row row2 = createRow(sheet, rowIndex);
			// 创建格
	
			Cell cell1 = createCell(row2, 0, "序号");
			Cell cell2 = createCell(row2, 1, "案件流水号");
			Cell cell3 = createCell(row2, 2, "案件状态");
			Cell cell4 = createCell(row2, 3, "公司代码");
			Cell cell5 = createCell(row2, 4, "业务员工号");
			Cell cell6 = createCell(row2, 5, "事故类型");
			Cell cell7 = createCell(row2, 6, "出险时间");
			Cell cell8 = createCell(row2, 7, "出险地点");
			Cell cell9 = createCell(row2, 8, "所属交警大队");
			Cell cell10 = createCell(row2, 9, "所属地市");
			Cell cell11 = createCell(row2, 10, "指引方式");
			Cell cell12 = createCell(row2, 11, "事故经过");
			Cell cell13 = createCell(row2, 12, "是否关联案件");
			Cell cell14 = createCell(row2, 13, "备注");
			Cell cell15 = createCell(row2, 14, "创建时间");
	
			cell1.setCellStyle(style4);
			cell2.setCellStyle(style4);
			cell3.setCellStyle(style4);
			cell4.setCellStyle(style4);
			cell5.setCellStyle(style4);
			cell6.setCellStyle(style4);
			cell7.setCellStyle(style4);
			cell8.setCellStyle(style4);
			cell9.setCellStyle(style4);
			cell10.setCellStyle(style4);
			cell11.setCellStyle(style4);
			cell12.setCellStyle(style4);
			cell13.setCellStyle(style4);
			cell14.setCellStyle(style4);
			cell15.setCellStyle(style4);
	
			int index = 1;
			for (TmblSurveyInfoEntity surveyInfo : list) {
				rowIndex++;
				int celIndex = -1;
				Row rowList = createRow(sheet, rowIndex);
	
				String caseStatus = "";
				if("T".equals(surveyInfo.getCaseStatus())){
					caseStatus = "未提交";
				}else if("S".equals(surveyInfo.getCaseStatus())){
					caseStatus = "已提交";
				}else if("C".equals(surveyInfo.getCaseStatus())){
					caseStatus = "注销";
				}else if("F".equals(surveyInfo.getCaseStatus())){
					caseStatus = "已完成";
				}
				
				
				Cell cellList0 = createCell(rowList, celIndex + 1, (index++) + "");
				Cell cellList1 = createCell(rowList, celIndex + 2,
						surveyInfo.getReportNo());
				Cell cellList2 = createCell(rowList, celIndex + 3,
						caseStatus);
				//公司代码
				TSDepart depart = appService.findUniqueByProperty(TSDepart.class, "orgCode",surveyInfo.getEmpDptCde());
				Cell cellList3 = createCell(rowList, celIndex + 4,
						depart.getDepartname());
						
				Cell cellList4 = createCell(rowList, celIndex + 5,
						surveyInfo.getAppUser().getEmpName());
				Cell cellList5 = createCell(rowList, celIndex + 6,
						surveyInfo.getAccidentType());
				Cell cellList6 = createCell(rowList, celIndex + 7,
						surveyInfo.getDangerTime());
				Cell cellList7 = createCell(rowList, celIndex + 8,
						surveyInfo.getDangerPlace());
				Cell cellList8 = createCell(rowList, celIndex + 9,
						surveyInfo.getTrafficPolice());
				Cell cellList9 = createCell(rowList, celIndex + 10,
						surveyInfo.getCity());
				Cell cellList10 = createCell(rowList, celIndex + 11,
						surveyInfo.getGuideWay());
				
				//事故经过
				//获取父类
				TSTypegroup tg=appService.findUniqueByProperty(TSTypegroup.class, "typegroupcode", "accident");
				String accidentName=null;
				for(TSType tc:tg.getTSTypes()){//父类中有个获取其所有子类的list方法，遍历这些list
					if(tc.getTypecode().equals(surveyInfo.getAccidentAfter())){//判断赋值
						accidentName=tc.getTypename();
					}
				}
				Cell cellList11 = createCell(rowList, celIndex + 12,
						accidentName);
				
				Cell cellList12 = createCell(rowList, celIndex + 13,
						surveyInfo.getIfCase());
				Cell cellList13 = createCell(rowList, celIndex + 14,
						surveyInfo.getRemark());
				Cell cellList14 = createCell(rowList, celIndex + 15,
						new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(surveyInfo.getCreateTm()));
	
				cellList0.setCellStyle(style2);
				cellList1.setCellStyle(style2);
				cellList2.setCellStyle(style2);
				cellList3.setCellStyle(style2);
				cellList4.setCellStyle(style2);
				cellList5.setCellStyle(style2);
				cellList6.setCellStyle(style2);
				cellList7.setCellStyle(style2);
				cellList8.setCellStyle(style2);
				cellList9.setCellStyle(style2);
				cellList10.setCellStyle(style2);
				cellList11.setCellStyle(style2);
				cellList12.setCellStyle(style2);
				cellList13.setCellStyle(style2);
				cellList14.setCellStyle(style2);
	
			}

		wb.write(os);
		os.close();
		}catch(Exception e){
			throw e;
		}finally{
			if(os!=null){
				os.close();
			}
		}
	}
	
	
	public String getStringValue(double i) {
		String s = "";
		if (i == 0) {
			s = "";
		} else {
			s = String.valueOf(i);
		}
		return s;
	}

	public String getfStringValue(Float f) {
		String s = "";
		if (f == 0.00) {
			s = "";
		} else {
			s = String.valueOf(f);
		}
		return s;
	}
	
}
